#!/usr/bin/env node

/**
 * 分析预约管理表的数据分布
 *
 * 使用方法：
 *   node app/scripts/analyze-appointment-data.mjs
 *   node app/scripts/analyze-appointment-data.mjs --db-path "C:\path\to\app.db"
 */

import { existsSync } from 'node:fs'
import { dirname, join } from 'node:path'
import { fileURLToPath } from 'node:url'

const __filename = fileURLToPath(import.meta.url)
const __dirname = dirname(__filename)
const projectRoot = join(__dirname, '..')

async function main() {
  console.log('='.repeat(60))
  console.log('分析 appointment_management 表数据分布')
  console.log('='.repeat(60))
  console.log()

  // 解析命令行参数
  const args = process.argv.slice(2)
  let specifiedDbPath = null
  for (let i = 0; i < args.length; i++) {
    if (args[i] === '--db-path' && i + 1 < args.length) {
      specifiedDbPath = args[i + 1]
      break
    }
  }

  // 初始化数据库
  console.log('正在初始化数据库...')
  const { PrismaClient } = await import('@prisma/client')

  let dbPath = null

  // 如果用户指定了数据库路径，直接使用
  if (specifiedDbPath) {
    if (existsSync(specifiedDbPath)) {
      dbPath = specifiedDbPath
      console.log(`使用指定的数据库文件: ${dbPath}`)
    }
    else {
      console.error(`❌ 指定的数据库文件不存在: ${specifiedDbPath}`)
      process.exit(1)
    }
  }
  else {
    // 尝试多个可能的数据库路径（优先使用生产环境）
    const possibleDbPaths = []

    // 如果是Windows，优先使用生产环境路径
    if (process.platform === 'win32') {
      // 优先使用生产环境实际路径
      possibleDbPaths.push('C:\\Users\\Administrator\\AppData\\Roaming\\AutoTools\\data\\app.db')

      const appData = process.env.APPDATA || process.env.LOCALAPPDATA
      if (appData) {
        // 使用AutoTools（生产环境实际使用的路径）
        possibleDbPaths.push(join(appData, 'AutoTools', 'data', 'app.db'))
        // 兼容其他可能的路径
        possibleDbPaths.push(join(appData, 'auto-tools', 'data', 'app.db'))
      }
    }

    // 最后尝试开发环境：项目目录下的数据库
    possibleDbPaths.push(join(projectRoot, 'prisma', 'data', 'app.db'))

    // 查找存在的数据库文件
    for (const path of possibleDbPaths) {
      if (existsSync(path)) {
        dbPath = path
        console.log(`找到数据库文件: ${dbPath}`)
        break
      }
    }

    if (!dbPath) {
      console.error('❌ 未找到数据库文件')
      process.exit(1)
    }
  }

  const dbUrl = `file:${dbPath.replace(/\\/g, '/')}`
  process.env.DATABASE_URL = dbUrl

  const prisma = new PrismaClient({
    datasources: {
      db: { url: dbUrl },
    },
  })

  // 启用 WAL 模式
  try {
    await prisma.$executeRawUnsafe('PRAGMA journal_mode=WAL;')
    await prisma.$executeRawUnsafe('PRAGMA synchronous=NORMAL;')
    await prisma.$executeRawUnsafe('PRAGMA busy_timeout=15000;')
  }
  catch {}

  console.log(`✅ 数据库初始化完成`)
  console.log()

  try {
    // 总记录数
    const totalCount = await prisma.appointment_management.count()
    console.log(`📊 总记录数: ${totalCount}`)
    console.log()

    // 按状态统计
    console.log('按 appointment_status 统计:')
    const statuses = ['pending', 'booked', 'failed', 'cancelled']
    for (const status of statuses) {
      const count = await prisma.appointment_management.count({
        where: { appointment_status: status },
      })
      console.log(`  - ${status}: ${count} 条`)
    }
    console.log()

    // 按 is_placeholder 统计（使用原生SQL避免null值问题）
    console.log('按 is_placeholder (占号数据) 统计:')
    try {
      const placeholderTrueResult = await prisma.$queryRawUnsafe('SELECT COUNT(*) as count FROM appointment_management WHERE is_placeholder = 1;')
      const placeholderFalseResult = await prisma.$queryRawUnsafe('SELECT COUNT(*) as count FROM appointment_management WHERE is_placeholder = 0;')
      const placeholderNullResult = await prisma.$queryRawUnsafe('SELECT COUNT(*) as count FROM appointment_management WHERE is_placeholder IS NULL;')

      const placeholderTrue = Array.isArray(placeholderTrueResult) && placeholderTrueResult[0] ? Number(placeholderTrueResult[0].count) : 0
      const placeholderFalse = Array.isArray(placeholderFalseResult) && placeholderFalseResult[0] ? Number(placeholderFalseResult[0].count) : 0
      const placeholderNull = Array.isArray(placeholderNullResult) && placeholderNullResult[0] ? Number(placeholderNullResult[0].count) : 0

      console.log(`  - is_placeholder = true (占号数据): ${placeholderTrue} 条`)
      console.log(`  - is_placeholder = false (普通预约): ${placeholderFalse} 条`)
      console.log(`  - is_placeholder = null: ${placeholderNull} 条`)
      console.log()
    }
    catch  {
      console.log('  ⚠️  查询 is_placeholder 失败，可能该字段不存在')
      console.log()
    }

    // 组合统计：占号数据 vs 普通预约（按状态，使用原生SQL）
    console.log('组合统计:')
    try {
      for (const status of statuses) {
        const placeholderResult = await prisma.$queryRawUnsafe(
          `SELECT COUNT(*) as count FROM appointment_management WHERE appointment_status = ? AND is_placeholder = 1;`,
          status,
        )
        const normalResult = await prisma.$queryRawUnsafe(
          `SELECT COUNT(*) as count FROM appointment_management WHERE appointment_status = ? AND (is_placeholder = 0 OR is_placeholder IS NULL);`,
          status,
        )

        const placeholderCount = Array.isArray(placeholderResult) && placeholderResult[0] ? Number(placeholderResult[0].count) : 0
        const normalCount = Array.isArray(normalResult) && normalResult[0] ? Number(normalResult[0].count) : 0

        console.log(`  ${status} 状态:`)
        console.log(`    - 占号数据: ${placeholderCount} 条`)
        console.log(`    - 普通预约: ${normalCount} 条`)
      }
      console.log()
    }
    catch  {
      console.log('  ⚠️  组合统计查询失败')
      console.log()
    }

    // 检查 is_monitor 字段
    try {
      const rows = await prisma.$queryRawUnsafe('PRAGMA table_info(\'appointment_management\');')
      const hasMonitor = Array.isArray(rows) && rows.some((r) => {
        const name = r && typeof r === 'object' && 'name' in r ? String(r.name || '') : ''
        return name === 'is_monitor'
      })
      if (hasMonitor) {
        console.log('按 is_monitor (监控数据) 统计:')
        const monitorTrue = await prisma.$queryRawUnsafe('SELECT COUNT(*) as count FROM appointment_management WHERE is_monitor = 1;')
        const monitorFalse = await prisma.$queryRawUnsafe('SELECT COUNT(*) as count FROM appointment_management WHERE is_monitor = 0 OR is_monitor IS NULL;')
        const monitorTrueArray = Array.isArray(monitorTrue) ? monitorTrue : []
        const monitorFalseArray = Array.isArray(monitorFalse) ? monitorFalse : []
        const monitorCount = monitorTrueArray[0] && monitorTrueArray[0].count ? Number(monitorTrueArray[0].count) : 0
        const nonMonitorCount = monitorFalseArray[0] && monitorFalseArray[0].count ? Number(monitorFalseArray[0].count) : 0
        console.log(`  - is_monitor = 1 (监控数据): ${monitorCount} 条`)
        console.log(`  - is_monitor = 0/null (非监控): ${nonMonitorCount} 条`)
        console.log()
      }
    }
    catch  {
      // 忽略错误
    }

    // 总结：普通预约（非占号、非监控）的数量
    console.log('='.repeat(60))
    console.log('总结:')
    try {
      const normalResult = await prisma.$queryRawUnsafe('SELECT COUNT(*) as count FROM appointment_management WHERE is_placeholder = 0 OR is_placeholder IS NULL;')
      const placeholderResult = await prisma.$queryRawUnsafe('SELECT COUNT(*) as count FROM appointment_management WHERE is_placeholder = 1;')

      const normalAppointments = Array.isArray(normalResult) && normalResult[0] ? Number(normalResult[0].count) : 0
      const placeholderAppointments = Array.isArray(placeholderResult) && placeholderResult[0] ? Number(placeholderResult[0].count) : 0

      console.log(`普通预约记录（is_placeholder = false/null）: ${normalAppointments} 条`)
      console.log(`占号数据记录（is_placeholder = true）: ${placeholderAppointments} 条`)
      console.log(`总计: ${totalCount} 条`)
    }
    catch  {
      console.log('⚠️  总结统计查询失败')
    }
    console.log('='.repeat(60))
  }
  finally {
    await prisma.$disconnect()
    console.log('\n✅ 数据库连接已关闭')
  }
}

main().catch((e) => {
  console.error('脚本执行失败:', e)
  process.exit(1)
})
